﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.Sql;
using System.Threading;
namespace SearchData
{
    public partial class DataSearch : Form
    {

        private BackgroundWorker m_AsyncWorker;
        private DateTime begin;
        private DataTable tables;
        private DateTime end;
        public string dbName;
        private int timeElapsed = 0;
        private TimeSpan elapsedSpan;
        public delegate void RetrunData();
        private string ConnectionStr = string.Empty;
        private string IncludedTables = string.Empty;
        private System.Windows.Forms.Timer tm = new System.Windows.Forms.Timer();
        // private string ExcludedTables = string.Empty;
        public DataSearch()
        {
            InitializeComponent();
            m_AsyncWorker = new BackgroundWorker();
            m_AsyncWorker.WorkerReportsProgress = true;
            m_AsyncWorker.WorkerSupportsCancellation = true;
            m_AsyncWorker.DoWork += new DoWorkEventHandler(m_AsyncWorker_DoWork);
          //  m_AsyncWorker.ProgressChanged += new ProgressChangedEventHandler(m_AsyncWorker_ProgressChanged);
            m_AsyncWorker.RunWorkerCompleted += new RunWorkerCompletedEventHandler(m_AsyncWorker_RunWorkerCompleted);
            tm.Tick += new EventHandler(tm_Tick);

        }

        void m_AsyncWorker_ProgressChanged(object sender, ProgressChangedEventArgs e)
        {
            //prTimer.Value = e.ProgressPercentage;
        }
        public void Shilpa()
        {
            grdData.DataSource = tables;
        }
        void m_AsyncWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            try
            {
                end = DateTime.Now;
                timeElapsed = 0;
                tm.Stop();
               // prTimer.Visible = false;
                if ((e.Cancelled == true))
                {
                    lblStatus.Text = "Process is cancelled";
                }
                else if (!(e.Error == null))
                {
                    lblStatus.Text = ("Error: " + e.Error.Message);
                }
                else
                {
                    TimeSpan span = new TimeSpan();
                    span = end - begin;
                    lblStatus.ForeColor = Color.Green;
                    lblStatus.Text = "Process Completed. Total Time Taken " + span.TotalSeconds + " seconds";
                }
                grdData.DataSource = tables;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void m_AsyncWorker_DoWork(object sender, DoWorkEventArgs e)
        {

            try
            {
                // BackgroundWorker worker = sender as BackgroundWorker;
                int n = Convert.ToInt32(e.Argument);
              //  prTimer.Value = n;
                //  string dbName = 
                GetDataProcess(m_AsyncWorker, e, dbName);//(n, worker, e);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void chkWindowsAuthentication_CheckedChanged(object sender, EventArgs e)
        {

            try
            {
                if (chkWindowsAuthentication.Checked)
                {
                    lblUserId.Visible = false;
                    lblPassword.Visible = false;
                    txtUser.Visible = false;
                    txtPassword.Visible = false;
                }
                else
                {

                    lblUserId.Visible = true;
                    lblPassword.Visible = true;
                    txtUser.Visible = true;
                    txtPassword.Visible = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btnGetDataBase_Click(object sender, EventArgs e)
        {
            try
            {
                if (ValidateData())
                {

                    this.lstDatabases.Items.Clear();

                    this.lstDatabases.Text = "Please wait while loading available databases...";
                    //this.ddlDatabases.SelectedIndex = 0;
                    DataTable tables = new DataTable("Tables");
                    using (IDbConnection connection = this.GetConnection())
                    {
                        IDbCommand command = connection.CreateCommand();
                        command.CommandText = "SELECT name FROM sys.Databases ORDER BY name ASC";
                        connection.Open();
                        tables.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                    }
                    this.lstDatabases.Items.Clear();
                    foreach (DataRow row in tables.Rows)
                        this.lstDatabases.Items.Add(row[0].ToString());

                    this.lstDatabases.SelectedIndex = 1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }




        private bool ValidateData()
        {
            if (chkWindowsAuthentication.Checked == false && txtUser.Text.Trim().Length > 0 && txtPassword.Text.Trim().Length > 0)
            {
                return true;
            }
            else if (chkWindowsAuthentication.Checked)
            {
                return true;
            }
            MessageBox.Show("If windows authentication is not used then username and password is required");
            return false;
        }

        private IDbConnection GetConnection()
        {
            string str1 = "Integrated Security=true";
            string dbName = "(local)";
            if (cmbServerName.Text.Trim().Length > 0)
            {
                dbName = cmbServerName.Text.Trim();
            }
            if (!chkWindowsAuthentication.Checked)
            {
                str1 = "Integrated Security=false; User Id=" + txtUser.Text.Trim() + "; Password=" + txtPassword.Text.Trim() + "; Connection Timeout=1500";
            }
            IDbConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=" + dbName + ";" + str1;
            return con;
        }

        private void btnGetData_Click(object sender, EventArgs e)
        {
            try
            {
                begin = DateTime.Now;
               //prTimer.Visible = true;
               tm.Start();               
                if (IsValidForm())
                {
                    IncludedTables = "";
                    IncludedTables = GetIncludedTables();
                    // ExcludedTables = GetExcludedTables();
                    InitiateDataSearch();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        void tm_Tick(object sender, EventArgs e)
        {
            //m_AsyncWorker.ReportProgress(((System.Windows.Forms.Timer)(sender)).Interval);
            //tm.Interval
            long elapsedTicks = DateTime.Now.Ticks - begin.Ticks;
           // timeElapsed += 1;
            elapsedSpan = new TimeSpan(elapsedTicks);
            if(elapsedSpan.Seconds<60)
                lblTimeElapsed.Text = "Time Elapsed : " + elapsedSpan.Seconds.ToString() + " Seconds";
            if(elapsedSpan.Seconds>60 && elapsedSpan.Minutes <=60)
                lblTimeElapsed.Text = "Time Elapsed : " + elapsedSpan.Minutes.ToString() + " Minutes";
            if (elapsedSpan.Minutes > 60)
                lblTimeElapsed.Text = "Time Elapsed : " + elapsedSpan.Hours.ToString() + " Hours";

        }

        private string GetIncludedTables()
        {
            StringBuilder sb = new StringBuilder();
            string query = "";
            if ((rdoExclude.Checked || rdoSelected.Checked) && lstTables.SelectedItems.Count <= 0)
            {
                MessageBox.Show("Select atleast one table");
                sb.Append("");
                return "";
            }
            else if (rdoAll.Checked)
            {
                return "";
            }
            else
            {
                foreach (object item in lstTables.SelectedItems)
                {
                    sb.Append("'" + item.ToString() + "',");
                }
                query = sb.ToString().Remove(sb.Length - 1, 1);
                if (rdoSelected.Checked)
                    query = "AND T.Name IN (" + query + ")";
                else if (rdoExclude.Checked)
                    query = "AND T.Name NOT IN (" + query + ")";

                return query;
            }

        }

        private bool IsValidForm()
        {
            if (lstDatabases.Text.Trim().Length > 0 && txtData.Text.Trim().Length > 0)
                return true;
            else
            {
                MessageBox.Show("Database Name and Data to Search is required!");
                return false;
            }
        }

        private void InitiateDataSearch()
        {
            if (tables != null && tables.Rows.Count > 0)
                tables.Rows.Clear();
            tables = new DataTable();
            DataRow dr1 = tables.NewRow();
            tables.Columns.Add("Column1");
            tables.Columns.Add("Column2");
            dr1[0] = " ";
            dr1[1] = " ";
            tables.Rows.Add(dr1);
            if (!m_AsyncWorker.IsBusy)
            {
                dbName = lstDatabases.Text.Trim();
                m_AsyncWorker.RunWorkerAsync();
                lblStatus.ForeColor = Color.Red;
                lblStatus.Text = "Wait .. while system is retrieving data!!";
                //  grdData.DataSource = tables;
            }
            RetrunData R = new RetrunData(Shilpa);
            R.Invoke();
        }

        private void GetDataProcess(BackgroundWorker worker, DoWorkEventArgs e, string dbName)
        {
            DataSet ds = new DataSet();
            if (worker.CancellationPending)
            {
                e.Cancel = true;
            }
            else
            {
                tables = new DataTable("Tables");

                DataTable tablestemp = new DataTable();
                tables.Columns.Add("Column1");
                tables.Columns.Add("Column2");
                DataRow dr;


                //using (IDbConnection connection = this.GetConnectionData(dbName))
                //{
                //    IDbCommand command = connection.CreateCommand();
                //    command.CommandText = GetQuery();
                //    connection.Open();
                //    tablestemp.Load(command.ExecuteReader(CommandBehavior.Default));
                //    //  grdData.DataSource = tables;

                //}
                try
                {

                    SqlDataAdapter da = null;
                    SqlCommand cmd = new SqlCommand();
                    cmd.Connection = new SqlConnection(ConnectionStr);
                    cmd.CommandTimeout = 0;
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = GetQuery();
                    da = new SqlDataAdapter(cmd);
                    da.Fill(ds);                   

                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                //  this.ddlDatabases.Items.Clear();
                if (ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        foreach (DataRow row in dt.Rows)
                        {
                            dr = tables.NewRow();
                            dr[0] = row[0];
                            dr[1] = row[1];
                            tables.Rows.Add(dr);
                        }
                    }
                }
                else
                {
                    dr = tables.NewRow();
                    dr[0] = " ";
                    dr[1] = " ";
                    tables.Rows.Add(dr);
                    MessageBox.Show("No Data Found.");
                }


                //   grdData.DataSource = tables;

            }
        }

        private IDbConnection GetConnectionData(string databaseName)
        {
            string str1 = "Integrated Security=true";
            string dbName = "(local)";
            if (cmbServerName.Text.Trim().Length > 0)
            {
                dbName = cmbServerName.Text.Trim();
            }
            if (!chkWindowsAuthentication.Checked)
            {
                str1 = "Integrated Security=false; User Id=" + txtUser.Text.Trim() + "; Password=" + txtPassword.Text.Trim();
            }
            IDbConnection con = new SqlConnection();
            con.ConnectionString = "Data Source=" + dbName + ";" + str1 + "; database=" + databaseName + ";Connection Timeout=0";
            return con;
        }

        private string GetConnectionData(string databaseName, string name)
        {
            string str1 = "Integrated Security=true";
            string dbName = "(local)";
            if (cmbServerName.Text.Trim().Length > 0)
            {
                dbName = cmbServerName.Text.Trim();
            }
            if (!chkWindowsAuthentication.Checked)
            {
                str1 = "Integrated Security=false; User Id=" + txtUser.Text.Trim() + "; Password=" + txtPassword.Text.Trim();
            }

            string ConnectionString = "Data Source=" + dbName + ";" + str1 + "; database=" + databaseName + ";Connection Timeout=0";
            return ConnectionString;
        }

        private void DataSearch_Load(object sender, EventArgs e)
        {
            try
            {
                cmbServerName.Text = System.Net.Dns.GetHostName().Trim() + "\\SQLEXPRESS";
                // GetDefaultDatabases();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private static void GetDefaultDatabases()
        {
            DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    MessageBox.Show(dr[0].ToString());
                }
            }
        }


        private string GetQuery()
        {
            string sbQuery = " DECLARE @TableName VARCHAR(100) " + Environment.NewLine + "" +
                                " DECLARE @ColName VARCHAR(100) " + Environment.NewLine + "" +
                                " DECLARE @Query NVARCHAR(MAX) " + Environment.NewLine + "" +
                                " DECLARE GetDetails CURSOR FOR " + Environment.NewLine + "" +
                                " SELECT T.Name ,C.name FROM sys.columns C INNER JOIN sys.tables T on C.object_id = T.object_id where T.type='U' " + IncludedTables + "" + Environment.NewLine + "" +
                               " OPEN GetDetails " + Environment.NewLine + "" +
                               " FETCH NEXT FROM GetDetails  " + Environment.NewLine + "" +
                               " INTO @TableName, @ColName   " + Environment.NewLine + "" +
                               " WHILE @@FETCH_STATUS = 0" + Environment.NewLine + "" +
                               " BEGIN  " + Environment.NewLine + "" +
                               "     SET @Query = 'IF ( (select count(*) from INFORMATION_SCHEMA.COLUMNS where DATA_TYPE<>''xml'' and COLUMN_NAME='''+@ColName+''')>0 ) BEGIN  IF(( SELECT count(*) FROM  '+@TableName+' WHERE CAST(['+@ColName+'] AS NVARCHAR(MAX)) LIKE ''%" + txtData.Text.Trim() + "%'')>0) SELECT DISTINCT '''+@TableName+''','''+@ColName+''' FROM  '+@TableName+' WHERE CAST(['+@ColName+'] AS NVARCHAR(MAX)) LIKE ''%" + txtData.Text.Trim() + "%'' END' " + Environment.NewLine + "" +
                               "     EXECUTE SP_EXECUTESQL @Query " + Environment.NewLine + "" +
                               "     FETCH NEXT FROM GetDetails  " + Environment.NewLine + "" +
                               "     INTO @TableName, @ColName  " + Environment.NewLine + "" +
                               " END " + Environment.NewLine + "" +
                              "  CLOSE GetDetails; " + Environment.NewLine + "" +
                              "  DEALLOCATE GetDetails;  ";

            return sbQuery;
        }

        private void lstDatabases_SelectedIndexChanged(object sender, EventArgs e)
        {
            try
            {
                if (ValidateData())
                {
                    ConnectionStr = GetConnectionData(lstDatabases.Text, "");
                    this.lstTables.Items.Clear();

                    this.lstTables.Text = "Please wait while loading available databases...";
                    //this.ddlDatabases.SelectedIndex = 0;
                    DataTable tables1 = new DataTable("Tables");
                    using (IDbConnection connection = this.GetConnectionData(lstDatabases.Text.Trim()))
                    {
                        IDbCommand command = connection.CreateCommand();
                        command.CommandText = "SELECT name FROM sys.tables WHERE type='U' ORDER BY name ASC";
                        connection.Open();
                        tables1.Load(command.ExecuteReader(CommandBehavior.CloseConnection));
                    }
                    this.lstTables.Items.Clear();
                    foreach (DataRow row in tables1.Rows)
                        this.lstTables.Items.Add(row[0].ToString());

                    //this.lstDatabases.SelectedIndex = 1;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
    }
}
